package io.mft.commons.db;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.db.Session;
import io.mft.commons.db.source.DbProvider;
import io.mft.commons.function.response.kv.KVMap;

public class BatchExecuteUtil {
	static Logger logger=LoggerFactory.getLogger(BatchExecuteUtil.class);
	public static int[] execute(Session session,String tbName, List<KVMap> parameters) throws SQLException {
		List<String> columns=parseColumns(parameters);
		Object[][] args=new Object[parameters.size()][columns.size()];
		int i=0;
		for (KVMap data : parameters) {
			int k=0;
			for (String column : columns) {
				args[i][k]=data.get(column);
				k++;
			}
			i++;
		}
		String sql=buildInsertSqL(tbName,columns);
		return session.executeBatch(sql, args);
	}
	public static int[] executeUpdate(Session session,String tbName,String[] whereField, List<KVMap> parameters) throws SQLException {
		List<String> columns=parseColumns(parameters);
		List<String> newColumns=new ArrayList<>();
		List<String> whereColumns=new ArrayList<>();
		Map<String, String> whereFieldMap=new HashMap<String, String>();
		for (String where : whereField) {
			whereFieldMap.put(where, where);
			whereColumns.add(where);
		}
		for (String col : columns) {
			if (!whereFieldMap.containsKey(col)) {
				newColumns.add(col);
			}
		}
		List<Object[]> argList=new ArrayList<>();
		
		
		List<String> argsColumns=new ArrayList<>();
		argsColumns.addAll(newColumns);
		argsColumns.addAll(whereColumns);
		for (KVMap data : parameters) {
			Object[] dataArgs=new Object[argsColumns.size()];
			int k=0;

			boolean isNeed=true;
			for (String column : argsColumns) {
				Object v=data.get(column);
				if (whereFieldMap.containsKey(column)) {
					if (ObjectUtil.isNull(v)) {
						isNeed=false;
						break;
					}
				}
				dataArgs[k]=v;
				k++;
			}
			if (isNeed) {
				argList.add(dataArgs);
			}
		}
		Object[][] args=new Object[argList.size()][argsColumns.size()];
		argList.toArray(args);
		String sql=buildUpdateSqL(tbName, whereField, newColumns);
		return session.executeBatch(sql, args);
	}
	
	
	public static int[] executeDelete(Session session,String tbName,String[] whereField, List<KVMap> parameters) throws SQLException {
		List<String> whereColumns=new ArrayList<>();
		Map<String, String> whereFieldMap=new HashMap<String, String>();
		for (String where : whereField) {
			whereFieldMap.put(where, where);
			whereColumns.add(where);
		}
		List<Object[]> argList=new ArrayList<>();
		List<String> argsColumns=new ArrayList<>();
		argsColumns.addAll(whereColumns);
		for (KVMap data : parameters) {
			Object[] dataArgs=new Object[argsColumns.size()];
			int k=0;
			boolean isNeed=true;
			for (String column : argsColumns) {
				Object v=data.get(column);
				if (ObjectUtil.isNull(v)) {
					isNeed=false;
					break;
				}
				dataArgs[k]=v;
				k++;
			}
			if (isNeed) {
				argList.add(dataArgs);
			}
		}
		Object[][] args=new Object[argList.size()][argsColumns.size()];
		argList.toArray(args);
		String sql=buildDeleteSqL(tbName, whereField);
		return session.executeBatch(sql, args);
	}
	public static int[] execute(String datasourceId,String tbName, List<KVMap> parameters) {
		Session session=null;
		try {
			session=DbProvider.getInstance().getSession(datasourceId);
			session.beginTransaction();
			int[] ss= execute(session, tbName, parameters);
			session.commit();
			return ss;
		}catch (Exception e) {
			logger.error("",e);
			try {
				session.rollback();
			} catch (SQLException e1) {
				logger.error("",e1);
			}
			return null;
		} finally {
			if (session!=null) {
				session.close();
			}
		}
		
	}
	public static int[] executeUpdate(String datasourceId,String tbName,String[] whereField, List<KVMap> parameters) {
		Session session=null;
		try {
			session=DbProvider.getInstance().getSession(datasourceId);
			session.beginTransaction();
			int[] ss= executeUpdate(session, tbName, whereField, parameters);
			session.commit();
			return ss;
		}catch (Exception e) {
			logger.error("",e);
			try {
				session.rollback();
			} catch (SQLException e1) {
				logger.error("",e1);
			}
			return null;
		} finally {
			if (session!=null) {
				session.close();
			}
		}
		
	}
	
	public static int[] executeDelete(String datasourceId,String tbName,String[] whereField, List<KVMap> parameters) {
		Session session=null;
		try {
			session=DbProvider.getInstance().getSession(datasourceId);
			session.beginTransaction();
			int[] ss= executeDelete(session, tbName, whereField, parameters);
			session.commit();
			return ss;
		}catch (Exception e) {
			logger.error("",e);
			try {
				session.rollback();
			} catch (SQLException e1) {
				logger.error("",e1);
			}
			return null;
		} finally {
			if (session!=null) {
				session.close();
			}
		}
		
	}
    protected static String buildUpdateSqL(String tableName,String[] whereField, List<String> columns)
    {
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("update " + tableName + " set ");
        int i = 0;
        for (String column : columns) {
            i++;
            if ( i < columns.size() )
            {
                sqlBuffer.append(column + " =?,");
            }
            else
            {
                sqlBuffer.append(column+" =?");
            }
        }
        sqlBuffer.append(" where ");
        String whereSql = "";
        int k=0;
        for (String where : whereField) {
        	k++;
        	if (k<whereField.length) {
        		whereSql+=where+" =? and ";
			}else {
				whereSql+=where+" =? ";
			}
		}
        sqlBuffer.append(whereSql);
        return sqlBuffer.toString();
    }
    
    protected static String buildDeleteSqL(String tableName,String[] whereField)
    {
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("delete from " + tableName);
        sqlBuffer.append(" where ");
        String whereSql = "";
        int k=0;
        for (String where : whereField) {
        	k++;
        	if (k<whereField.length) {
        		whereSql+=where+" =? and ";
			}else {
				whereSql+=where+" =? ";
			}
		}
        sqlBuffer.append(whereSql);
        return sqlBuffer.toString();
    }
    protected static String buildInsertSqL(String tableName, List<String> columns)
    {
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("insert into " + tableName + "(");
        String interrogationStr = "";
        int i = 0;
        for (String column : columns) {
            i++;
            if ( i < columns.size() )
            {
                sqlBuffer.append(column + ",");
                interrogationStr += " ?,";
            }
            else
            {
                sqlBuffer.append(column);
                interrogationStr += "?";
            }
        }
        sqlBuffer.append(") values (");
        sqlBuffer.append(interrogationStr);
        sqlBuffer.append(") ");
        return sqlBuffer.toString();
    }
	public static List<String> parseColumns(List<KVMap> parameterList) {
		HashMap<String, String> cols=new HashMap<String, String>();
		for (KVMap KVMap : parameterList) {
			 Set<?> keys=KVMap.keySet();
			 for (Object object : keys) {
				cols.put(String.valueOf(object), String.valueOf(object));
			}
		}
		List<String> colums=new ArrayList<String>(cols.values());
		return colums;
	}
}
